Re: Returning multiple Rows from PL/pgSQL-Function

Hi,

> So - basically you want something like:
> 
> SELECT * from emotions
> WHERE emotion_date <= [cutoff time]
> ORDER BY calculated_score(date_epoch,full_rating)
> LIMIT 300

yes, thats it -- nearly :-)

In detail the calculated_score is:

(cutoff_time - creation_time) + (sum_of_rating_points * rating_factor)


> Where you'd have an index on "calculated_score". Well - you can either
> have a "score" field and use triggers to keep it up to date 

yes, this was also my first idea, but this depends also on the [cutoff
time], so it can't work, because the trigger can't update this.



> or build an
> index on the "calculated_score()" function. Depends on your pattern of
> usage which is going to be better for you.
> 
> You can create a functional index as easily as a normal one:

aaah, wow, that's cool, I didn't know this before.


but here is the same: also the resulting order can't precalculated, if i'm
not completely wrong.


> Is that the sort of thing you were after?

nearly ;-)

In detail, I have the following:

Users enter from time to time some values (their "emotions") and place some
dots with this. Each dot has somethinglike a lifetime relative to the other
dots; this lifetime depends on the creation date and some rating of other
users.
Additionally it is possible to go back in the timeline and visit the stuff
from an earlyer view, but with new Voting. Always 300 dots are shown, but
not only the newest ones, there is also a chance to be viewed longer with
besser voting. 


It works now -- with the two statements I posted in the last message. It's
not exact because of the first select of 3000 dots -- if because of good
voting the 3001th dot should be visible it isn't, but thats not critical. 


Ciao Alvar


-- 
| AGI ............................................................... |
| Magirusstrasse 21B, 70469 Stuttgart . Fon +49 (0)711.228 74-50 .... |
| http://www.agi.com/diary/ (english) . http://www.agi.de/tagebuch/ . |
| >>>>>> NEWS >>> AGI holt Bronze-Loewen in Cannes! <<<<<<<<<<<<<<<<< |